import time
from io import BytesIO
from typing import Union
from typing import List
from sqlalchemy import text
from sqlalchemy import and_
from sqlalchemy import func
from urllib.parse import unquote, quote
import xlsxwriter
from apis import api_routes
from public.local import date_type, local_time
from datetime import datetime, timedelta
from models.course.detailed import Detailed
from flask import g, request, jsonify, abort, Response
from models.facility.facility import Facility
from security.decoration import certification
from models.owner.owner import Owner


@api_routes.route("/capacity/station", methods=["GET"])
# @certification(auth=True, role=None)
def func_capacity_station_get_api():
    """
    报表统计每个工站产能, 完成API开发任务
    http://127.0.0.1:8080/api/capacity/station
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"         string      开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"        string      结束时间，默认今天时间  格式 %Y-%m-%d
    "aircraft_id"   Int         机种名称,(下拉，键值对) api get http://127.0.0.1:8080/api/aircraft/all
    "owner_id"      Int         默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        {'status': 200, 'data': resp}
        {'status': 4001, 'msg': "选择班次不能跨天查询"}
    ---
    tags:
      - 设备工站数据统计
    """
    start: Union[datetime, str, None] = request.args.get("start", default=None, type=str)
    finish: Union[datetime, str, None] = request.args.get("finish", default=None, type=str)
    aircraft_id: int = request.args.get("aircraft_id", default=0, type=int)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    stations: list = []
    parm: Union[str, None] = None
    facility: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for itr in facility:
        if itr.uuid4 is not None:
            stations.append(itr.id)

    sql_totals: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 {}
        ) AS temps 
    """

    sql_standard: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND status = 't' {}
        ) AS temps 
    """

    sql_failure: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND status = 'f' {}
        ) AS temps 
    """

    sql_totals = sql_totals.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_totals.format("")
    sql_standard = sql_standard.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_standard.format("")
    sql_failure = sql_failure.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_failure.format("")

    resp: list = []
    for index, itr in enumerate(stations):
        facility: Facility = g.session.query(Facility).filter(Facility.id == itr).first()
        db_totals, = g.session.execute(text(sql_totals), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()
        standard, = g.session.execute(text(sql_standard), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()
        failure, = g.session.execute(text(sql_failure), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()

        resp.append({
            "serials": facility.serials,
            "name": facility.name,
            "totals": db_totals,
            "standard": standard,
            "failure": failure
        })
    docs: dict = {
        "serials": "设备序列",
        "name": "设备名称",
        "totals": "生产数量",
        "standard": "合格数量",
        "failure": "不合格数量",
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/capacity/station/output", methods=["GET"])
def func_capacity_station_output_get_api():
    """
    报表统计每个工站产能导出EXCEL, 完成API开发任务
    http://127.0.0.1:8080/api/capacity/station/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"         string      开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"        string      结束时间，默认今天时间  格式 %Y-%m-%d
    "aircraft_id"   Int         机种名称,(下拉，键值对) api get http://127.0.0.1:8080/api/aircraft/all
    "owner_id"      Int         默认(下拉，键值对)，可以不选, 排班名称，1白班， 2晚班
    }
    :return:
        {'status': 200, 'data': resp}
        {'status': 4001, 'msg': "选择班次不能跨天查询"}
    ---
    tags:
      - 设备工站数据统计
    """
    start: Union[datetime, str, None] = request.args.get("start", default=None, type=str)
    finish: Union[datetime, str, None] = request.args.get("finish", default=None, type=str)
    aircraft_id: int = request.args.get("aircraft_id", default=0, type=int)
    owner_id: int = request.args.get("owner_id", default=0, type=int)

    if owner_id not in [0, 1, 2]:
        abort(code=400)

    if start is not None and len(start) == 0:
        start = None
    if finish is not None and len(finish) == 0:
        finish = None

    if (owner_id and start is None) or (owner_id and finish is None) or (owner_id and start != finish):
        return jsonify({'status': 4001, 'msg': "选择班次不能跨天查询或未指定日期"})

    back_start = start
    back_finish = finish

    if start is not None:
        start = datetime.strptime(start, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M:%S")
    if finish is not None:
        temp_date_time = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    db_owner: Union[Owner, None] = g.session.query(Owner).filter(Owner.id == owner_id).first()
    if db_owner is not None and db_owner.start <= db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        finish = "{} {}".format(back_finish, db_owner.finish.strftime("%H:%M:%S"))
    elif db_owner is not None and db_owner.start > db_owner.finish:
        start = "{} {}".format(back_start, db_owner.start.strftime("%H:%M:%S"))
        hour = db_owner.finish.hour
        temp_date_time = datetime.strptime(back_finish, "%Y-%m-%d") + timedelta(days=1) + timedelta(hours=hour)
        finish = temp_date_time.strftime("%Y-%m-%d %H:%M:%S")

    stations: list = []
    parm: Union[str, None] = None
    facility: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for itr in facility:
        if itr.uuid4 is not None:
            stations.append(itr.id)

    sql_totals: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 {}
        ) AS temps 
    """

    sql_standard: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND status = 't' {}
        ) AS temps 
    """

    sql_failure: str = """
        SELECT
            COUNT(temps.sequence)
        FROM ( 
            SELECT
                 sequence
            FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND status = 'f' {}
        ) AS temps 
    """

    sql_totals = sql_totals.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_totals.format("")
    sql_standard = sql_standard.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_standard.format("")
    sql_failure = sql_failure.format("AND aircraft_id = {}".format(aircraft_id)) if aircraft_id > 0 else sql_failure.format("")

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史报警统计表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "设备编号", "生产总数量", "合格数量", "不合格数量", "产品合格率"]
    worksheet_index.write_row(row=0, col=0, data=message)

    for index, itr in enumerate(stations):
        facility: Facility = g.session.query(Facility).filter(Facility.id == itr).first()
        db_totals, = g.session.execute(text(sql_totals), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()
        standard, = g.session.execute(text(sql_standard), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()
        failure, = g.session.execute(text(sql_failure), {"arg0": start, "arg1": finish, "ft_id": itr}).fetchone()
        sequence = index + 1
        init_data_ok: Union[str, None] = None
        if db_totals > 0:
            init_data_ok = str(round((standard / db_totals) * 100, 2)) + "%"

        t_array = [facility.name, facility.serials, db_totals, standard, failure, init_data_ok]
        worksheet_index.write_row(row=sequence, col=0, data=t_array)

    file_name = "{}.xlsx".format(local_time())
    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200


@api_routes.route("/capacity/station/details", methods=["GET"])
def func_capacity_station_details_get_api():
    """
    报表统计每个工站产能, NG加工后再NG 完成API开发任务
    http://127.0.0.1:8080/api/capacity/station/details
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"     string            开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"    string            结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 设备工站数据统计
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None or len(start) <= 0:
        arg0 = date_type()
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    parm: Union[str, None] = None
    stations = []
    facility: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for itr in facility:
        if itr is not None and itr.uuid4 is not None:
            stations.append(itr.id)

    if finish is None or len(finish) <= 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    # sql_totals: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id AND
    #         numbers = 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """
    sql_totals: str = """
        SELECT
            COUNT(temp0.sequence) as numbers 
        FROM (
        SELECT
            sequence
        FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
        ) AS temp0
    """

    # sql_failure_first: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id  AND status = 'f' AND
    #         numbers = 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """
    sql_failure_first: str = """
        SELECT 
            COUNT(temp1.count_value) as numbers
        FROM(
        SELECT
            count_value, sequence
        FROM (
        SELECT
                COUNT(id) as count_value, sequence
            FROM detailed WHERE facility_id = :ft_id AND status = 'f' AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
        ) AS temp0
        ) AS temp1 WHERE temp1.count_value = 1
    """

    # sql_failure_double: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id  AND status = 'f' AND
    #         numbers > 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """
    sql_failure_double: str = """
        SELECT 
            COUNT(temp1.count_value) as numbers
        FROM(
            SELECT
                count_value, sequence
            FROM (
                SELECT
                    COUNT(id) as count_value, sequence
                FROM detailed WHERE facility_id = :ft_id AND status = 'f' AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
            ) AS temp0
        ) AS temp1 WHERE temp1.count_value > 1
    """

    resp: list = []
    for index, itr in enumerate(stations):
        query_parm: dict = {"arg0": arg0, "arg1": arg1, "ft_id": itr}
        facility: Facility = g.session.query(Facility).filter(Facility.id == itr).first()
        db_totals, = g.session.execute(text(sql_totals), query_parm).fetchone()
        failure_first, = g.session.execute(text(sql_failure_first), query_parm).fetchone()
        failure_double, = g.session.execute(text(sql_failure_double), query_parm).fetchone()

        resp.append({
            "serials": facility.serials,
            "name": facility.name,
            "totals": db_totals,
            "failure_first": failure_first,
            "failure_double": failure_double
        })
    docs: dict = {
        "serials": "设备序列",
        "name": "设备名称",
        "totals": "生产数量",
        "failure_first": "正常做不良率",
        "failure_double": "返工后不良率",
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/capacity/station/details/output", methods=["GET"])
def func_capacity_station_details_output_get_api():
    """
    报表统计每个工站产能导出EXCEL, NG加工后再NG 完成API开发任务
    http://127.0.0.1:8080/api/capacity/station/details/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"     string            开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"    string            结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 设备工站数据统计
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)

    arg0, arg1 = start, finish
    if start is None or len(start) <= 0:
        arg0 = date_type()
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    parm: Union[str, None] = None
    stations = []
    facility: List[Facility] = g.session.query(Facility).filter(Facility.uuid4 != parm).order_by(Facility.id).all()
    for itr in facility:
        if itr is not None and itr.uuid4 is not None:
            stations.append(itr.id)

    if finish is None or len(finish) <= 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    # sql_totals: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id AND
    #         numbers = 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """

    sql_totals: str = """
        SELECT
            COUNT(temp0.sequence) as numbers 
        FROM (
        SELECT
            sequence
        FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
        ) AS temp0
    """

    # sql_failure_first: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id  AND status = 'f' AND
    #         numbers = 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """

    sql_failure_first: str = """
        SELECT 
            COUNT(temp1.count_value) as numbers
        FROM(
        SELECT
            count_value, sequence
        FROM (
        SELECT
                COUNT(id) as count_value, sequence
            FROM detailed WHERE facility_id = :ft_id AND status = 'f' AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
        ) AS temp0
        ) AS temp1 WHERE temp1.count_value = 1
    """

    # sql_failure_double: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM (
    #         SELECT
    #              MAX(id), sequence
    #         FROM detailed WHERE facility_id = :ft_id  AND status = 'f' AND
    #         numbers > 1 AND time >= :arg0 AND time <= :arg1  GROUP BY sequence
    #     ) AS temps
    # """

    sql_failure_double: str = """
        SELECT 
            COUNT(temp1.count_value) as numbers
        FROM(
            SELECT
                count_value, sequence
            FROM (
                SELECT
                    COUNT(id) as count_value, sequence
                FROM detailed WHERE facility_id = :ft_id AND status = 'f' AND time >= :arg0 AND time <= :arg1 GROUP BY sequence
            ) AS temp0
        ) AS temp1 WHERE temp1.count_value > 1
    """

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})
    worksheet_index = workbook.add_worksheet("设备历史报警统计表")
    worksheet_index.set_column("A:ZZ", 32)
    message: list = ["设备名字", "设备编号", "总共生产数量", "1次不良数", "1次不良率", "返工后不良数", "返工后不良率"]
    worksheet_index.write_row(row=0, col=0, data=message)

    for index, itr in enumerate(stations):
        query_parm: dict = {"arg0": arg0, "arg1": arg1, "ft_id": itr}
        facility: Facility = g.session.query(Facility).filter(Facility.id == itr).first()
        db_totals, = g.session.execute(text(sql_totals), query_parm).fetchone()
        failure_first, = g.session.execute(text(sql_failure_first), query_parm).fetchone()
        failure_double, = g.session.execute(text(sql_failure_double), query_parm).fetchone()

        sequence = index + 1

        failure_1: Union[str, None] = None
        if db_totals > 0:
            failure_1 = str(round((failure_first / db_totals) * 100, 2)) + "%"
        failure_2: Union[str, None] = None
        if db_totals > 0:
            failure_2 = str(round((failure_double / db_totals) * 100, 2)) + "%"

        t_array = [facility.name, facility.serials, db_totals, failure_first, failure_1, failure_double, failure_2]
        worksheet_index.write_row(row=sequence, col=0, data=t_array)

    # """
    # EXPLAIN ANALYZE
    # SELECT
    # facility.name,
    # facility.serials,
    # (
    # SELECT
    #         COUNT(temp0.sequence) as numbers
    # FROM (
    # SELECT
    #         sequence
    # FROM detailed WHERE facility_id = facility.id AND time >= '2024-04-08' GROUP BY sequence
    # ) AS temp0
    # ),
    # (
    # SELECT
    # COUNT(temp1.count_value) as first_ng
    # FROM(
    # SELECT
    #     count_value, sequence
    # FROM (
    # SELECT
    #     COUNT(id) as count_value, sequence
    # FROM detailed WHERE facility_id = facility.id AND status = 'f' AND time >= '2024-04-08' GROUP BY sequence
    # ) AS temp0
    # ) AS temp1 WHERE temp1.count_value = 1
    # ),
    # (
    # SELECT
    # COUNT(temp1.count_value) as second_ng
    # FROM(
    # SELECT
    #     count_value, sequence
    # FROM (
    # SELECT
    #     COUNT(id) as count_value, sequence
    # FROM detailed WHERE facility_id = facility.id AND status = 'f' AND time >= '2024-04-08' GROUP BY sequence
    # ) AS temp0
    # ) AS temp1 WHERE temp1.count_value >= 2
    # )
    # FROM facility ORDER BY id
    # """

    file_name = "{}.xlsx".format(local_time())
    # 在数据流之前关闭工作薄
    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
